﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BookStore.Models
{
    public class CategoriesModel
    {
        private string connectionString;
        private SqlConnection connect;

        public CategoriesModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public string getCategoryByID(int ID)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL = "SELECT * FROM Categories WHERE category_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = reader["category_name"].ToString();
                
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public DataTable getAllCategories()
        {
            DataTable result = new DataTable();
            try
            {
                connect.Open();
                string selectSQL = "SELECT * FROM Categories Order By category_name ASC";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                result.Load(reader);
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
    }
}
